﻿
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Microsoft.Office.Interop
Imports System.IO



Public Class NoContracts

    Private dataAdapter As New OleDbDataAdapter

    Private Sub NoContracts_FormClosing(sender As Object, e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing

        GridView1.SaveLayoutToXml(My.Settings.ConfigPath & "\NoContractsW01.xml")
        My.Settings.NoContractsSize = Me.Size
        My.Settings.NoContractsPos = Me.Location

    End Sub

    '************************************************************************************************************************
    ' This view has to be createt on the server before you can use this fonction
    '************************************************************************************************************************

    'drop view JLD014
    'GO
    'create VIEW M3JDTAP001.JLD014   AS

    'select  SINDIV.SAINNO AS SerieNumber,
    'date(SUBSTRING(SAINda,1,4) || '-' ||  SUBSTRING(SAINda,5,2)||  '-' ||  SUBSTRING(SAINda,7,2) )  as InstallDate,
    'case when (OLMWTP is null) then
    'date(SUBSTRING(SAINda,1,4) || '-' ||  SUBSTRING(SAINda,5,2)||  '-' ||  SUBSTRING(SAINda,7,2) ) + 1 YEAR - 1 DAY
    'else
    'date(SUBSTRING(SAINda,1,4) || '-' ||  SUBSTRING(SAINda,5,2)||  '-' ||  SUBSTRING(SAINda,7,2) ) + INT(OLYEA1) YEAR + INT(OLMON1) MONTH - 1 DAY
    'end as EndofGarantie
    'from   SINDIV SINDIV LEFT OUTER JOIN SMWCON SMWCON ON SINDIV.SAINNO = SMWCON.OLIDE1
    'where SINDIV.SAAGRS = ''



    Private Sub NoContracts_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.Size = My.Settings.NoContractsSize
        Me.Location = My.Settings.NoContractsPos
    End Sub




    Function GetData(ByVal selectCommand As String)

        Try

            Dim ConCM As OleDb.OleDbConnection
            ConCM = New OleDb.OleDbConnection(My.Settings.M3Connect)
            ConCM.Open()

            Dim cmd As New OleDbCommand(selectCommand, ConCM)

            Me.dataAdapter = New OleDb.OleDbDataAdapter(cmd)
            Dim table As New DataTable
            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.dataAdapter.Fill(table)
            ConCM.Close()


            Me.BindingSource1.DataSource = table
            ' Resize the GridView columns to fit the newly loaded content.
            Me.GridView1.BestFitColumns()
            If File.Exists(My.Settings.ConfigPath & "\NoContractsW01.xml") Then GridView1.RestoreLayoutFromXml(My.Settings.ConfigPath & "\NoContractsW01.xml")

        Catch ex As OleDbException

        End Try
        GetData = ""

    End Function






    Private Sub BarButtonItem1_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick


        GetData("select * from jld014")
        'Info.Caption = "Number of positions: " & GridView1.RowCount.ToString


    End Sub

 
    Private Sub BarButtonItem2_ItemClick(sender As System.Object, e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
        'Load data to excel
        Const filename As String = "c:\JLD004_NoContracts.xls"

        If File.Exists(filename) Then
            File.Delete(filename)
        End If

        GridControl1.ExportToXls(filename)

        Dim app = New Microsoft.Office.Interop.Excel.Application
        app.Visible = True

        app.Workbooks.Open(System.IO.Path.GetFullPath(filename))
    End Sub
End Class